﻿using System;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using System.Xml.Linq;
using Slam.Configuration;
using Slam;

namespace SlamCms.SharePoint.SlamProfiles.ContentItem
{
	public class ContentItemProfileSection
	{
		#region Private State

		private static Regex _guidRegex = new Regex(@"^(\{){0,1}[0-9a-fA-F]{8}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{4}\-[0-9a-fA-F]{12}(\}){0,1}$", RegexOptions.Compiled);
		private XDocument _configXml;

		#endregion

		public ContentItemProfileSection(XDocument configurationXml)
		{
			_configXml = configurationXml;
		}

		public ContentItemConfiguredType GetContentItemConfiguredType(string siteCollection, string id, string typeName)
		{
			Guid guidId = Guid.Empty;
			XElement selectedNode = null;

			//Selection precedence follows:
			//If ID is present and a valid, Get type that matches ID
			//If no type matches ID, Get type that has TableName matching the typeName
			//Finally, if nothing yet has been selected get the first node that has Name matching the 
			//typeName

			//Check if ID is present and valid
			if (!String.IsNullOrEmpty(id) && _guidRegex.IsMatch(id))
			{
				guidId = new Guid(id);

				selectedNode = (from typeNode in _configXml.Root.Elements().Where(mapping =>
								   mapping.Name == "DataMapping" &&
								   (String.IsNullOrEmpty(siteCollection) ?
								   (mapping.Attribute("SiteCollection") == null ||
								   String.IsNullOrEmpty(mapping.Attribute("SiteCollection").Value)) :
								   (mapping.Attribute("SiteCollection") != null &&
								   mapping.Attribute("SiteCollection").Value.ToLower() == siteCollection.ToLower()))).Elements()
								where typeNode.Attribute("ID") != null &&
									  typeNode.Attribute("ID").Value == guidId.ToString()
								select typeNode).DefaultIfEmpty().First();
			}

			if (selectedNode == null)
			{
				//Get node matching TableName
				selectedNode = (from typeNode in _configXml.Root.Elements().Where(mapping =>
								   mapping.Name == "DataMapping" &&
								   (String.IsNullOrEmpty(siteCollection) ?
								   (mapping.Attribute("SiteCollection") == null ||
								   String.IsNullOrEmpty(mapping.Attribute("SiteCollection").Value)) :
								   (mapping.Attribute("SiteCollection") != null &&
								   mapping.Attribute("SiteCollection").Value.ToLower() == siteCollection.ToLower()))).Elements()
								where typeNode.Attribute("TableName") != null &&
									  typeNode.Attribute("TableName").Value == typeName
								select typeNode).DefaultIfEmpty().First();

				//If nothing has yet been selected
				if (selectedNode == null)
				{
					//Get node matching type
					selectedNode = (from typeNode in _configXml.Root.Elements().Where(mapping =>
									   mapping.Name == "DataMapping" &&
									   (String.IsNullOrEmpty(siteCollection) ?
									   (mapping.Attribute("SiteCollection") == null ||
									   String.IsNullOrEmpty(mapping.Attribute("SiteCollection").Value)) :
									   (mapping.Attribute("SiteCollection") != null &&
									   mapping.Attribute("SiteCollection").Value.ToLower() == siteCollection.ToLower()))).Elements()
									where typeNode.Attribute("Name").Value == typeName
									select typeNode).DefaultIfEmpty().First();
				}
			}

			ContentItemConfiguredType matchedType = new ContentItemConfiguredType();
			if (selectedNode != null)
			{
				matchedType.Name = selectedNode.Attribute("Name") == null ? "" : (string)selectedNode.Attribute("Name");
				matchedType.ContentItemType = selectedNode.Attribute("ContentItemType") == null ? "" : (string)selectedNode.Attribute("ContentItemType");
				matchedType.PreventDelete = selectedNode.Attribute("PreventDelete") == null ? false : (bool)selectedNode.Attribute("PreventDelete");
				matchedType.FrontEndUrl = selectedNode.Attribute("FrontEndUrl") == null ? "" : (string)selectedNode.Attribute("FrontEndUrl");
				matchedType.SlugFormat = selectedNode.Attribute("SlugFormat") == null ? "" : (string)selectedNode.Attribute("SlugFormat");
				matchedType.ContentItemOnly = selectedNode.Attribute("ContentItemOnly") == null ? false : selectedNode.Attribute("ContentItemOnly").Value.ToLower() == "true";
				matchedType.AllowFrontEndUrlCharacters = selectedNode.Attribute("AllowFrontEndUrlCharacters") == null ? "" : (string)selectedNode.Attribute("AllowFrontEndUrlCharacters");
				matchedType.TableName = selectedNode.Attribute("TableName") == null ? "" : (string)selectedNode.Attribute("TableName");
			}
			return matchedType;
		}

		public ContentItemConfiguredType GetContentItemConfiguredType(string siteCollection, string contentItemType)
		{
			XElement selectedNode = (from typeNode in _configXml.Root.Elements().Where(mapping =>
									   mapping.Name == "DataMapping" &&
									   (String.IsNullOrEmpty(siteCollection) ?
									   (mapping.Attribute("SiteCollection") == null ||
									   String.IsNullOrEmpty(mapping.Attribute("SiteCollection").Value)) :
									   (mapping.Attribute("SiteCollection") != null &&
									   mapping.Attribute("SiteCollection").Value.ToLower() == siteCollection.ToLower()))).Elements()
									 where typeNode.Attribute("ContentItemType") != null && typeNode.Attribute("ContentItemType").Value == contentItemType
									 select typeNode).DefaultIfEmpty().First();

			ContentItemConfiguredType matchedType = new ContentItemConfiguredType();
			if (selectedNode != null)
			{
				matchedType.Name = selectedNode.Attribute("Name") == null ? "" : (string)selectedNode.Attribute("Name");
				matchedType.ContentItemType = selectedNode.Attribute("ContentItemType") == null ? "" : (string)selectedNode.Attribute("ContentItemType");
				matchedType.FrontEndUrl = selectedNode.Attribute("FrontEndUrl") == null ? "" : (string)selectedNode.Attribute("FrontEndUrl");
				matchedType.SlugFormat = selectedNode.Attribute("SlugFormat") == null ? "" : (string)selectedNode.Attribute("SlugFormat");
				matchedType.ContentItemOnly = selectedNode.Attribute("ContentItemOnly") == null ? false : selectedNode.Attribute("ContentItemOnly").Value.ToLower() == "true";
				matchedType.AllowFrontEndUrlCharacters = selectedNode.Attribute("AllowFrontEndUrlCharacters") == null ? "" : (string)selectedNode.Attribute("AllowFrontEndUrlCharacters");
				matchedType.TableName = selectedNode.Attribute("TableName") == null ? "" : (string)selectedNode.Attribute("TableName");
			}
			return matchedType;
		}

		public SqlMappedAssociationField GetContentItemTagAssociationField()
		{
			IConfigurationManager configManager = ComponentService.GetInstance<IConfigurationManager>();
			XmlSqlDataMapping dataMapping = null;
			if (configManager.DataMapping is XmlSqlDataMapping)
			{
				dataMapping = (XmlSqlDataMapping)configManager.DataMapping;
			}
			return GetContentItemTagAssociationField(dataMapping);
		}

		public SqlMappedAssociationField GetContentItemTagAssociationField(XmlSqlDataMapping dataMapping)
		{
			TypeIdDefinition typeIdDefinition = new TypeIdDefinition();
			string typeIdDefinitionName = dataMapping.GetTypeIdDefinitionName();
			XmlTypeIdDefinitionSection typeIdDefininitionSection = new XmlTypeIdDefinitionSection(_configXml);

			if (String.IsNullOrEmpty(typeIdDefinitionName))
			{
				typeIdDefinition = typeIdDefininitionSection.Default;
			}
			else
			{
				typeIdDefinition = typeIdDefininitionSection.GetTypeIdDefinition(typeIdDefinitionName);
			}

			SqlMappedAssociationField contentItemTagAssociation = new SqlMappedAssociationField("ContentItemTag");
			contentItemTagAssociation.AssociationName = "ContentItemTag";
			contentItemTagAssociation.AssociatedTypeName = "Tag";
			contentItemTagAssociation.FirstAssociatedTypeIdColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentItem").Replace("{TableName}", "ContentItem"));
			contentItemTagAssociation.FirstAssociatedTypeIdSourceColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentItem").Replace("{TableName}", "ContentItem"));
			contentItemTagAssociation.FirstAssociatedTypeIdSqlType = typeIdDefinition.SqlType;
			contentItemTagAssociation.FirstAssociatedTypeTableName = "ContentItem";
			contentItemTagAssociation.SecondAssociatedTypeIdColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "Tag").Replace("{TableName}", "Tag"));
			contentItemTagAssociation.SecondAssociatedTypeIdSourceColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "Tag").Replace("{TableName}", "Tag"));
			contentItemTagAssociation.SecondAssociatedTypeIdSqlType = typeIdDefinition.SqlType;
			contentItemTagAssociation.SecondAssociatedTypeTableName = "Tag";
			contentItemTagAssociation.Required = true;

			return contentItemTagAssociation;
		}

		public SqlMappedAssociationField GetContentItemFeatureLocationAssociationField()
		{
			IConfigurationManager configManager = ComponentService.GetInstance<IConfigurationManager>();
			XmlSqlDataMapping dataMapping = null;
			if (configManager.DataMapping is XmlSqlDataMapping)
			{
				dataMapping = (XmlSqlDataMapping)configManager.DataMapping;
			}
			return GetContentItemFeatureLocationAssociationField(dataMapping);
		}

		public SqlMappedAssociationField GetContentItemFeatureOnTagAssociationField()
		{
			IConfigurationManager configManager = ComponentService.GetInstance<IConfigurationManager>();
			XmlSqlDataMapping dataMapping = null;
			if (configManager.DataMapping is XmlSqlDataMapping)
			{
				dataMapping = (XmlSqlDataMapping)configManager.DataMapping;
			}
			return GetContentItemFeatureOnTagAssociationField(dataMapping);
		}

		public SqlMappedAssociationField GetContentItemFeatureLocationAssociationField(XmlSqlDataMapping dataMapping)
		{
			TypeIdDefinition typeIdDefinition = new TypeIdDefinition();
			string typeIdDefinitionName = dataMapping.GetTypeIdDefinitionName();
			XmlTypeIdDefinitionSection typeIdDefininitionSection = new XmlTypeIdDefinitionSection(_configXml);

			if (String.IsNullOrEmpty(typeIdDefinitionName))
			{
				typeIdDefinition = typeIdDefininitionSection.Default;
			}
			else
			{
				typeIdDefinition = typeIdDefininitionSection.GetTypeIdDefinition(typeIdDefinitionName);
			}

			SqlMappedAssociationField contentItemFeatureLocationAssociation = new SqlMappedAssociationField("ContentItemFeatureLocation");
			contentItemFeatureLocationAssociation.AssociationName = "ContentItemFeatureLocation";
			contentItemFeatureLocationAssociation.AssociatedTypeName = "FeatureLocation";
			contentItemFeatureLocationAssociation.FirstAssociatedTypeIdColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentItem").Replace("{TableName}", "ContentItem"));
			contentItemFeatureLocationAssociation.FirstAssociatedTypeIdSourceColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentItem").Replace("{TableName}", "ContentItem"));
			contentItemFeatureLocationAssociation.FirstAssociatedTypeIdSqlType = typeIdDefinition.SqlType;
			contentItemFeatureLocationAssociation.FirstAssociatedTypeTableName = "ContentItem";
			contentItemFeatureLocationAssociation.SecondAssociatedTypeIdColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "FeatureLocation").Replace("{TableName}", "FeatureLocation"));
			contentItemFeatureLocationAssociation.SecondAssociatedTypeIdSourceColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "FeatureLocation").Replace("{TableName}", "FeatureLocation"));
			contentItemFeatureLocationAssociation.SecondAssociatedTypeIdSqlType = typeIdDefinition.SqlType;
			contentItemFeatureLocationAssociation.SecondAssociatedTypeTableName = "FeatureLocation";
			contentItemFeatureLocationAssociation.Required = true;

			return contentItemFeatureLocationAssociation;
		}

		public SqlMappedAssociationField GetContentItemFeatureOnTagAssociationField(XmlSqlDataMapping dataMapping)
		{
			TypeIdDefinition typeIdDefinition = new TypeIdDefinition();
			string typeIdDefinitionName = dataMapping.GetTypeIdDefinitionName();
			XmlTypeIdDefinitionSection typeIdDefininitionSection = new XmlTypeIdDefinitionSection(_configXml);

			if (String.IsNullOrEmpty(typeIdDefinitionName))
			{
				typeIdDefinition = typeIdDefininitionSection.Default;
			}
			else
			{
				typeIdDefinition = typeIdDefininitionSection.GetTypeIdDefinition(typeIdDefinitionName);
			}

			SqlMappedAssociationField contentItemTagAssociation = new SqlMappedAssociationField("ContentItemFeatureOnTag");
			contentItemTagAssociation.AssociationName = "ContentItemFeatureOnTag";
			contentItemTagAssociation.AssociatedTypeName = "Tag";
			contentItemTagAssociation.FirstAssociatedTypeIdColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentItem").Replace("{TableName}", "ContentItem"));
			contentItemTagAssociation.FirstAssociatedTypeIdSourceColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentItem").Replace("{TableName}", "ContentItem"));
			contentItemTagAssociation.FirstAssociatedTypeIdSqlType = typeIdDefinition.SqlType;
			contentItemTagAssociation.FirstAssociatedTypeTableName = "ContentItem";
			contentItemTagAssociation.SecondAssociatedTypeIdColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "Tag").Replace("{TableName}", "Tag"));
			contentItemTagAssociation.SecondAssociatedTypeIdSourceColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "Tag").Replace("{TableName}", "Tag"));
			contentItemTagAssociation.SecondAssociatedTypeIdSqlType = typeIdDefinition.SqlType;
			contentItemTagAssociation.SecondAssociatedTypeTableName = "Tag";
			contentItemTagAssociation.Required = true;

			return contentItemTagAssociation;
		}

		public IEnumerable<Field> GetContentItemFields()
		{
			IConfigurationManager configManager = ComponentService.GetInstance<IConfigurationManager>();
			XmlSqlDataMapping dataMapping = null;
			if (configManager.DataMapping is XmlSqlDataMapping)
			{
				dataMapping = (XmlSqlDataMapping)configManager.DataMapping;
			}
			return GetContentItemFields(dataMapping);
		}

		public IEnumerable<Field> GetContentItemFields(XmlSqlDataMapping dataMapping)
		{
			Guid guidId = Guid.Empty;
			XElement selectedNode = null;

			string connectionString = dataMapping.GetConnectionString();

			if (String.IsNullOrEmpty(connectionString))
			{
				connectionString = _configXml.Root.Element("ConnectionStrings").Attribute("Default").Value;
			}

			var ContentItemSections = from ContentItemNode in _configXml.Root.Elements().Where(node =>
											node.Name == "ContentItem")
									  select ContentItemNode;

			selectedNode = ContentItemSections.Where(c => c.Attribute("ConnectionString") != null && c.Attribute("ConnectionString").Value == connectionString).SingleOrDefault();
			if (selectedNode == null)
			{
				selectedNode = ContentItemSections.Where(c => c.Attribute("ConnectionString") == null).SingleOrDefault();
			}


			IEnumerable<SqlMappedField> fields =
				from field in selectedNode.Descendants("Field")
				where field.Attribute("AssociatedTypeName") == null
				select new SqlMappedField
				{
					Column = field.Attribute("Column") == null ? "" : (string)field.Attribute("Column"),
					Name = field.Attribute("Name") == null ? "" : (string)field.Attribute("Name"),
					SqlType = field.Attribute("SqlType") == null ? "" : (string)field.Attribute("SqlType"),
					Required = field.Attribute("Required") == null ? false : (string)field.Attribute("Required").Value.ToLower() == "true",
				};

			List<Field> fieldsToReturn = fields.Cast<Field>().ToList();

			//XmlSqlDataMapping dataMapping = new XmlSqlDataMapping(_configXml);

			TypeIdDefinition typeIdDefinition = new TypeIdDefinition();
			string typeIdDefinitionName = dataMapping.GetTypeIdDefinitionName();
			XmlTypeIdDefinitionSection typeIdDefininitionSection = new XmlTypeIdDefinitionSection(_configXml);

			if (String.IsNullOrEmpty(typeIdDefinitionName))
			{
				typeIdDefinition = typeIdDefininitionSection.Default;
			}
			else
			{
				typeIdDefinition = typeIdDefininitionSection.GetTypeIdDefinition(typeIdDefinitionName);
			}

			SqlMappedField listItemIdField = new SqlMappedField("UniqueId");
			listItemIdField.Column = "ListItemID";
			listItemIdField.Required = true;
			listItemIdField.SqlType = "uniqueidentifier";

			SqlMappedField listIdField = new SqlMappedField("ListID");
			listIdField.Column = "ListID";
			listIdField.Required = true;
			listIdField.SqlType = "uniqueidentifier";

			SqlMappedField listRelativeIdField = new SqlMappedField("ID");
			listRelativeIdField.Column = "ListRelativeID";
			listRelativeIdField.Required = true;
			listRelativeIdField.SqlType = "int";

			int indexForInsert = 0;
			if (!String.IsNullOrEmpty(typeIdDefinition.Name))
			{
				SqlMappedField idField = new SqlMappedField("SLAM ID");
				idField.Column = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentItem").Replace("{TableName}", "ContentItem"));
				idField.Required = true;
				idField.SqlType = typeIdDefinition.SqlType;

				fieldsToReturn.Insert(0, idField);
				fieldsToReturn.Insert(1, listItemIdField);
				fieldsToReturn.Insert(2, listIdField);
				fieldsToReturn.Insert(3, listRelativeIdField);
				indexForInsert = 4;
			}
			else
			{
				fieldsToReturn.Insert(0, listItemIdField);
				fieldsToReturn.Insert(1, listIdField);
				fieldsToReturn.Insert(2, listRelativeIdField);
				indexForInsert = 3;
			}

			SqlMappedAssociationField contentTypeAssociation = new SqlMappedAssociationField("ContentItemContentTypeID");
			contentTypeAssociation.AssociatedTypeName = "ContentType";
			contentTypeAssociation.FirstAssociatedTypeIdColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentType").Replace("{TableName}", "ContentType"));
			contentTypeAssociation.FirstAssociatedTypeIdSourceColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentType").Replace("{TableName}", "ContentType"));
			contentTypeAssociation.FirstAssociatedTypeIdSqlType = typeIdDefinition.SqlType;
			contentTypeAssociation.FirstAssociatedTypeTableName = "ContentItem";
			contentTypeAssociation.SecondAssociatedTypeIdColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentType").Replace("{TableName}", "ContentType"));
			contentTypeAssociation.SecondAssociatedTypeIdSourceColumn = FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentType").Replace("{TableName}", "ContentType"));
			contentTypeAssociation.SecondAssociatedTypeIdSqlType = typeIdDefinition.SqlType;
			contentTypeAssociation.SecondAssociatedTypeTableName = "ContentType";
			contentTypeAssociation.Required = true;
			fieldsToReturn.Insert(indexForInsert, contentTypeAssociation);
			indexForInsert++;

			SqlMappedField frontEndUrlField = new SqlMappedField("Front-End Url");
			frontEndUrlField.Column = "FrontEndUrl";
			frontEndUrlField.Required = true;
			frontEndUrlField.SqlType = "varchar(255)";
			fieldsToReturn.Insert(indexForInsert, frontEndUrlField);
			indexForInsert++;

			SqlMappedAssociationField statusAssociation = new SqlMappedAssociationField("StatusID");
			statusAssociation.AssociatedTypeName = "Status";
			statusAssociation.FirstAssociatedTypeIdColumn = "StatusID";
			statusAssociation.FirstAssociatedTypeIdSourceColumn = "StatusID";
			statusAssociation.FirstAssociatedTypeIdSqlType = "int";
			statusAssociation.FirstAssociatedTypeTableName = "ContentItem";
			statusAssociation.SecondAssociatedTypeIdColumn = "StatusID";
			statusAssociation.SecondAssociatedTypeIdSourceColumn = "StatusID";
			statusAssociation.SecondAssociatedTypeIdSqlType = "int";
			statusAssociation.SecondAssociatedTypeTableName = "Status";
			statusAssociation.Required = true;
			fieldsToReturn.Insert(indexForInsert, statusAssociation);

			SqlMappedField primaryIdField = new SqlMappedField("Primary SLAM ID"); //This name should not match a field in any list item
			primaryIdField.Column = "Primary" + FormatName(String.IsNullOrEmpty(typeIdDefinition.NameFormat) ? "ID" : typeIdDefinition.NameFormat.Replace("{TypeName}", "ContentItem").Replace("{TableName}", "ContentItem"));
			primaryIdField.Required = true;
			primaryIdField.SqlType = typeIdDefinition.SqlType;
			fieldsToReturn.Insert(1, primaryIdField);

			IEnumerable<SqlMappedAssociationField> associations =
				from field in selectedNode.Descendants("Field")
				where field.Attribute("AssociatedTypeName") != null
				select new SqlMappedAssociationField
				{
					AssociatedTypeName = (string)field.Attribute("AssociatedTypeName"),
					Name = (string)field.Attribute("Name"),
					Required = field.Attribute("Required") == null ? false : (string)field.Attribute("Required").Value.ToLower() == "true"
				};

			string firstTableName = "ContentItem";

			List<SqlMappedAssociationField> associationsToReturn = associations.ToList();
			foreach (SqlMappedAssociationField associationField in associationsToReturn)
			{
				SqlMappedField secondIdField = (SqlMappedField)dataMapping.GetTypeFields("", associationField.AssociatedTypeName).First(f => f is SqlMappedField);
				string secondTableName = dataMapping.GetTypeAttribute("", associationField.AssociatedTypeName, "TableName");
				if (String.IsNullOrEmpty(secondTableName))
				{
					secondTableName = FormatName(associationField.AssociatedTypeName);
				}
				associationField.FirstAssociatedTypeIdColumn = FormatName(((SqlMappedField)fieldsToReturn[0]).Column == secondIdField.Column ? "ContentItem" + ((SqlMappedField)fieldsToReturn[0]).Column : ((SqlMappedField)fieldsToReturn[0]).Column);
				associationField.FirstAssociatedTypeIdSourceColumn = FormatName(((SqlMappedField)fieldsToReturn[0]).Column);
				associationField.FirstAssociatedTypeIdSqlType = ((SqlMappedField)fieldsToReturn[0]).SqlType;
				associationField.FirstAssociatedTypeTableName = firstTableName;
				associationField.SecondAssociatedTypeIdColumn = FormatName(((SqlMappedField)fieldsToReturn[0]).Column == secondIdField.Column ? associationField.AssociatedTypeName + secondIdField.Column : secondIdField.Column);
				//If these columns have the same name it is likely that this association is a self-association
				if (associationField.FirstAssociatedTypeIdColumn == associationField.SecondAssociatedTypeIdColumn)
				{
					associationField.SecondAssociatedTypeIdColumn = associationField.SecondAssociatedTypeIdColumn + "2";
				}
				associationField.SecondAssociatedTypeIdSourceColumn = FormatName(secondIdField.Column);
				associationField.SecondAssociatedTypeIdSqlType = secondIdField.SqlType;
				associationField.SecondAssociatedTypeTableName = secondTableName;
			}

			fieldsToReturn.AddRange(associationsToReturn.Cast<Field>());

			SqlMappedAssociationField createdByAssociation = new SqlMappedAssociationField("CreatedByUserID");
			createdByAssociation.AssociatedTypeName = "User";
			createdByAssociation.FirstAssociatedTypeIdColumn = "UserID";
			createdByAssociation.FirstAssociatedTypeIdSourceColumn = "UserID";
			createdByAssociation.FirstAssociatedTypeIdSqlType = "int";
			createdByAssociation.FirstAssociatedTypeTableName = "ContentItem";
			createdByAssociation.SecondAssociatedTypeIdColumn = "CreatedByUserID";
			createdByAssociation.SecondAssociatedTypeIdSourceColumn = "UserID";
			createdByAssociation.SecondAssociatedTypeIdSqlType = "int";
			createdByAssociation.SecondAssociatedTypeTableName = "User";
			createdByAssociation.Required = true;
			fieldsToReturn.Add(createdByAssociation);

			SqlMappedAssociationField modifiedByAssociation = new SqlMappedAssociationField("ModifiedByUserID");
			modifiedByAssociation.AssociatedTypeName = "User";
			modifiedByAssociation.FirstAssociatedTypeIdColumn = "UserID";
			modifiedByAssociation.FirstAssociatedTypeIdSourceColumn = "UserID";
			modifiedByAssociation.FirstAssociatedTypeIdSqlType = "int";
			modifiedByAssociation.FirstAssociatedTypeTableName = "ContentItem";
			modifiedByAssociation.SecondAssociatedTypeIdColumn = "ModifiedByUserID";
			modifiedByAssociation.SecondAssociatedTypeIdSourceColumn = "UserID";
			modifiedByAssociation.SecondAssociatedTypeIdSqlType = "int";
			modifiedByAssociation.SecondAssociatedTypeTableName = "User";
			modifiedByAssociation.Required = true;
			fieldsToReturn.Add(modifiedByAssociation);

			SqlMappedField createdDateField = new SqlMappedField("Created");
			createdDateField.Column = "CreatedDate";
			createdDateField.Required = true;
			createdDateField.SqlType = "datetime";
			fieldsToReturn.Add(createdDateField);

			SqlMappedField modifiedDateField = new SqlMappedField("Modified");
			modifiedDateField.Column = "ModifiedDate";
			modifiedDateField.Required = true;
			modifiedDateField.SqlType = "datetime";
			fieldsToReturn.Add(modifiedDateField);

			SqlMappedField referenceTitleField = new SqlMappedField("ReferenceTitle");
			referenceTitleField.Column = "ReferenceTitle";
			referenceTitleField.Required = true;
			referenceTitleField.SqlType = "varchar(255)";
			fieldsToReturn.Add(referenceTitleField);

			SqlMappedField slugField = new SqlMappedField("Slug");
			slugField.Column = "Slug";
			slugField.Required = true;
			slugField.SqlType = "varchar(255)";
			fieldsToReturn.Add(slugField);

			return fieldsToReturn;
		}

		public IEnumerable<ClearCacheUrl> GetClearCacheUrls()
		{
			XElement selectedNode = null;

			IConfigurationManager configManager = ComponentService.GetInstance<IConfigurationManager>();
			string connectionString = "";
			if (configManager.DataMapping is XmlSqlDataMapping)
			{
				connectionString = ((XmlSqlDataMapping)configManager.DataMapping).GetConnectionString();
			}

			if (String.IsNullOrEmpty(connectionString))
			{
				connectionString = _configXml.Root.Element("ConnectionStrings").Attribute("Default").Value;
			}

			var ContentItemSections = from ContentItemNode in _configXml.Root.Elements().Where(node =>
											node.Name == "ContentItem")
									  select ContentItemNode;

			selectedNode = ContentItemSections.Where(c => c.Attribute("ConnectionString") != null && c.Attribute("ConnectionString").Value == connectionString).SingleOrDefault();
			if (selectedNode == null)
			{
				selectedNode = ContentItemSections.Where(c => c.Attribute("ConnectionString") == null).SingleOrDefault();
			}

			IEnumerable<ClearCacheUrl> clearCacheUrls = from node in selectedNode.Descendants("ClearCacheUrl")
														select new ClearCacheUrl
														{
															AuthType = node.Attribute("AuthType") == null ? "" : node.Attribute("AuthType").Value,
															Domain = node.Attribute("Domain") == null ? "" : node.Attribute("Domain").Value,
															Password = node.Attribute("Password") == null ? "" : node.Attribute("Password").Value,
															UserName = node.Attribute("UserName") == null ? "" : node.Attribute("UserName").Value,
															Url = node.Attribute("Url") == null ? "" : node.Attribute("Url").Value,
															Uri = node.Attribute("Uri") == null ? "" : node.Attribute("Uri").Value,
														};

			return clearCacheUrls;
		}

		public string GetGlobalIdPrefix()
		{
			XElement selectedNode = null;

			IConfigurationManager configManager = ComponentService.GetInstance<IConfigurationManager>();
			string connectionString = "";
			if (configManager.DataMapping is XmlSqlDataMapping)
			{
				connectionString = ((XmlSqlDataMapping)configManager.DataMapping).GetConnectionString();
			}

			if (String.IsNullOrEmpty(connectionString))
			{
				connectionString = _configXml.Root.Element("ConnectionStrings").Attribute("Default").Value;
			}

			var ContentItemSections = from ContentItemNode in _configXml.Root.Elements().Where(node =>
											node.Name == "ContentItem")
									  select ContentItemNode;

			selectedNode = ContentItemSections.Where(c => c.Attribute("ConnectionString") != null && c.Attribute("ConnectionString").Value == connectionString).SingleOrDefault();
			if (selectedNode == null)
			{
				selectedNode = ContentItemSections.Where(c => c.Attribute("ConnectionString") == null).SingleOrDefault();
			}

			return selectedNode == null ? "g" : selectedNode.Attribute("GlobalIdPrefix").Value;
		}

		public IEnumerable<ContentItemField> GetTypeContentItemFields(string siteCollection, string id, string typeName)
		{
			Guid guidId = Guid.Empty;
			XElement selectedNode = null;

			//Selection precedence follows:
			//If ID is present and a valid, Get type that matches ID
			//If no type matches ID, Get type that has TableName matching the typeName
			//Finally, if nothing yet has been selected get the first node that has Name matching the 
			//typeName

			//Check if ID is present and valid
			if (!String.IsNullOrEmpty(id) && _guidRegex.IsMatch(id))
			{
				guidId = new Guid(id);

				selectedNode = (from typeNode in _configXml.Root.Elements().Where(mapping =>
								   mapping.Name == "DataMapping" &&
								   (String.IsNullOrEmpty(siteCollection) ?
								   (mapping.Attribute("SiteCollection") == null ||
								   String.IsNullOrEmpty(mapping.Attribute("SiteCollection").Value)) :
								   (mapping.Attribute("SiteCollection") != null &&
								   mapping.Attribute("SiteCollection").Value.ToLower() == siteCollection.ToLower()))).Elements()
								where typeNode.Attribute("ID") != null &&
									  typeNode.Attribute("ID").Value == guidId.ToString()
								select typeNode).DefaultIfEmpty().First();
			}

			if (selectedNode == null)
			{
				//Get node matching TableName
				selectedNode = (from typeNode in _configXml.Root.Elements().Where(mapping =>
								   mapping.Name == "DataMapping" &&
								   (String.IsNullOrEmpty(siteCollection) ?
								   (mapping.Attribute("SiteCollection") == null ||
								   String.IsNullOrEmpty(mapping.Attribute("SiteCollection").Value)) :
								   (mapping.Attribute("SiteCollection") != null &&
								   mapping.Attribute("SiteCollection").Value.ToLower() == siteCollection.ToLower()))).Elements()
								where typeNode.Attribute("TableName") != null &&
									  typeNode.Attribute("TableName").Value == typeName
								select typeNode).DefaultIfEmpty().First();

				//If nothing has yet been selected
				if (selectedNode == null)
				{
					//Get node matching type
					selectedNode = (from typeNode in _configXml.Root.Elements().Where(mapping =>
									   mapping.Name == "DataMapping" &&
									   (String.IsNullOrEmpty(siteCollection) ?
									   (mapping.Attribute("SiteCollection") == null ||
									   String.IsNullOrEmpty(mapping.Attribute("SiteCollection").Value)) :
									   (mapping.Attribute("SiteCollection") != null &&
									   mapping.Attribute("SiteCollection").Value.ToLower() == siteCollection.ToLower()))).Elements()
									where typeNode.Attribute("Name").Value == typeName
									select typeNode).DefaultIfEmpty().First();
				}
			}

			IEnumerable<ContentItemField> fields =
				from field in selectedNode.Descendants("Field")
				where field.Attribute("ContentItemField") != null
				select new ContentItemField
				{
					Name = field.Attribute("Name") == null ? "" : (string)field.Attribute("Name"),
					Column = field.Attribute("Column") == null ? Regex.Replace((field.Attribute("Name") == null ? "" : (string)field.Attribute("Name")), "[\\W\\s]", "") : (string)field.Attribute("Column"),
					ContentItemName = field.Attribute("ContentItemField") == null ? "" : (string)field.Attribute("ContentItemField"),
				};

			return fields;
		}

		private static string FormatName(string name)
		{
			return Regex.Replace(name, "[\\W\\s]", "");
		}
	}

	public class ClearCacheUrl
	{
		public string AuthType { get; set; }
		public string Domain { get; set; }
		public string UserName { get; set; }
		public string Password { get; set; }
		public string Uri { get; set; }
		public string Url { get; set; }
	}
}
